package cn.com.edu.nju.ecrec.dataProcess;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class DataClean {
	public static void deleteDuplicate() {
		try {
			String driverName = "com.mysql.jdbc.Driver";
			String userName = "root";
			String userPasswd = "123456";
			String url = "jdbc:mysql://localhost:3307/ECRec";
			Class.forName(driverName).newInstance();
			Connection con = DriverManager.getConnection(url, userName,
					userPasswd);
			Statement statement = con.createStatement();
			String sql1 = "create table b (id bigint(20))";
			String sql2 = "insert into b (select min(id) as id from useritem group by infoID,pid)";
			String sql3 = "delete from useritem where id not in (select id from b)";
			String sql4 = "drop table b";
			statement.execute(sql1);
			statement.execute(sql2);
			statement.execute(sql3);
			statement.execute(sql4);

			statement.close();
			con.close();
			System.out.println("delete duplicate completed.");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static void cleanItem() {
		try {
			String driverName = "com.mysql.jdbc.Driver";
			String userName = "root";
			String userPasswd = "123456";
			String url = "jdbc:mysql://localhost:3307/ECRec";
			Class.forName(driverName).newInstance();
			Connection con = DriverManager.getConnection(url, userName,
					userPasswd);
			Statement statement = con.createStatement();
			String sql = "delete from item where pid not in (select pid from useritem)";

			statement.execute(sql);

			statement.close();
			con.close();
			System.out.println("clean item completed.");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static void cleanUser(){
		try {
			String driverName = "com.mysql.jdbc.Driver";
			String userName = "root";
			String userPasswd = "123456";
			String url = "jdbc:mysql://localhost:3307/ECRec";
			Class.forName(driverName).newInstance();
			Connection con = DriverManager.getConnection(url, userName,
					userPasswd);
			Statement statement = con.createStatement();
			
			String sql = "delete from userinfo where refer_url=''";

			statement.execute(sql);

			statement.close();
			con.close();
			System.out.println("clean user completed.");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public static void cleanUser2(){
		try {
			String driverName = "com.mysql.jdbc.Driver";
			String userName = "root";
			String userPasswd = "123456";
			String url = "jdbc:mysql://localhost:3307/ECRec";
			Class.forName(driverName).newInstance();
			Connection con = DriverManager.getConnection(url, userName,
					userPasswd);
			Statement statement = con.createStatement();
			
			String sql1 = "delete from userinfo where infoID not in (select infoID from useritem)";
			String sql2 = "delete from userinfo_refpara where USERINFO_INFOID not in (select infoID from userinfo)";
			String sql3 = "delete from refpara where id not in (select PARAS_ID from userinfo_refpara)";

			statement.execute(sql1);
			statement.execute(sql2);
			statement.execute(sql3);

			statement.close();
			con.close();
			System.out.println("clean user2 completed.");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public static void main(String[] args) {
		//deleteDuplicate();
		//cleanItem();
		//cleanUser();
		cleanUser2();
	}
}
